﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using CNCSoft.EData;
using System.Data.SqlClient;
using Common;

namespace CNCSoft.ErpApps.TMS.Academic
{
    public partial class frmDiemDanh_New : Form
    {
        #region(Biến toàn cục)
        DataSet _ds;
        string _strCon;
        DataRelation _master_detail;
        #endregion

        public frmDiemDanh_New()
        {
            InitializeComponent();
            _strCon = GlobalVariable.WSInfo.ConnectionString;
            _ds = new DataSet();
        }

        private void frmDiemDanh_New_Load(object sender, EventArgs e)
        {
            try
            {
                //init for dataset
                _ds.Tables.Add("LOP_READY");
                _ds.Tables.Add("GIAOVIEN_READY");

                _ds.Tables.Add("MASTER");
                _ds.Tables.Add("DETAIL");

                //call method load LOP => Gan DM_LOP ready cho LookupEdit
                this.LoadLopByTrangThai();

                repository_TilePhanTram.DisplayFormat.FormatString = "N2";

                gvGiaoVien.IndicatorWidth = 50;  //Kich thuoc tieu de hang
                gvHocSinh.IndicatorWidth = 50;  //Kich thuoc tieu de hang
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


        private void GetDataByLop(string MaLop)
        {
            SqlConnection cnn = new SqlConnection(_strCon);

            try
            {
                if (_ds.Tables["DETAIL"] != null)
                {
                    _ds.Tables["DETAIL"].Clear();
                }
                if (_ds.Tables["MASTER"] != null)
                {
                    _ds.Tables["MASTER"].Clear();
                }

                //mapping data to MASTER
                SqlParameter[] sqlP_MaLop = new SqlParameter[1];
                sqlP_MaLop[0] = new SqlParameter("@MaKhoaHoc", MaLop);
                SQLHelper.Fill(_ds.Tables["MASTER"], _strCon, CommandType.StoredProcedure, "[dbo].[sp_frmDiemDanh_GetDataStartUp]", sqlP_MaLop);

                //mapping data to DETAIL
                SqlParameter[] sqlP_D = new SqlParameter[1];
                sqlP_D[0] = new SqlParameter("@MaKhoaHoc", MaLop);
                SQLHelper.Fill(_ds.Tables["DETAIL"], _strCon, CommandType.StoredProcedure, "[dbo].[sp_frmDiemDanh_GetHocSinhByLop]", sqlP_D);
                _ds.Tables["DETAIL"].Columns["ID"].AutoIncrement = true;
                _ds.Tables["DETAIL"].Columns["ID"].AutoIncrementStep = 1;

                //SET PRIMARY KEY
                _ds.Tables["MASTER"].PrimaryKey = new DataColumn[] { _ds.Tables["MASTER"].Columns["MaHocTap"] };
                _ds.Tables["DETAIL"].PrimaryKey = new DataColumn[] { _ds.Tables["DETAIL"].Columns["ID"] };

                //SET RELATIONSHIP
                if (_ds.Relations.Count == 0)
                    _master_detail = _ds.Relations.Add("M_D", _ds.Tables["MASTER"].Columns["MaHocTap"], _ds.Tables["DETAIL"].Columns["MaHocTap"]);

                //bind for grid MASTER
                gcGiaoVien.DataSource = _ds;
                gcGiaoVien.DataMember = "MASTER";

                //bind data to gridcontrol HOCSINH
                gcHocSinh.DataSource = _ds;
                gcHocSinh.DataMember = "MASTER.M_D";
                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        //method load LOP ready
        private void LoadLopByTrangThai() 
        {
            //clear data before bind data 
            if (_ds.Tables["LOP_READY"] != null)
            {
                _ds.Tables["LOP_READY"].Clear();
            }

            try
            {
                //mapping data to DATASET
                SqlParameter[] sqlP = new SqlParameter[1];
                sqlP[0] = new SqlParameter("@TrangThai", "Ready");
                SQLHelper.Fill(_ds.Tables["LOP_READY"], _strCon, CommandType.StoredProcedure, "[dbo].[sp_LoadLopByTrangThai]", sqlP);
            }
            catch (Exception ex) 
            {
                MessageBox.Show(ex.Message);
            }

            //bind data to control
            cboKhoaHoc.Properties.DataSource = _ds.Tables["LOP_READY"];
            cboKhoaHoc.Properties.DisplayMember = "TenKhoaHoc";
            cboKhoaHoc.Properties.ValueMember = "MaKhoaHoc";
        }

        //method load GIAOVIEN READY BY LOP
        private void LoadGiaoVienByLop(string malop)
        {       
                //clear data before bind data 
            if (_ds.Tables["GIAOVIEN_READY"] != null)
            {
                _ds.Tables["GIAOVIEN_READY"].Clear();
            }

            try
            {
                //mapping data to DATASET
                SqlParameter[] sqlP = new SqlParameter[1];
                sqlP[0] = new SqlParameter("@MaKhoaHoc", malop);
                SQLHelper.Fill(_ds.Tables["GIAOVIEN_READY"], _strCon, CommandType.StoredProcedure, "[dbo].[sp_LoadGiaoVienByLop]", sqlP);
            }
            catch (Exception ex) 
            {
                MessageBox.Show(ex.Message);
            }

            //bind data to control
            repIGiaoVien.DataSource = _ds.Tables["GIAOVIEN_READY"];
            repIGiaoVien.DisplayMember = "TenGiaoVien";
            repIGiaoVien.ValueMember = "MaGiaoVien";
        }

        
        //method execute query into MASTER
        private Boolean ExecuteIntoMaster() 
        {
            //Declare param and assign value
            SqlParameter[] pInsert = new SqlParameter[6];
            SqlParameter[] pUpdate = new SqlParameter[6];
            SqlParameter[] pDelete = new SqlParameter[1];

            pInsert[0] = new SqlParameter("@MaHocTap", SqlDbType.UniqueIdentifier, 18, "MaHocTap");
            pInsert[1] = new SqlParameter("@MaKhoaHoc", SqlDbType.VarChar, 20, "MaKhoaHoc");
            pInsert[2] = new SqlParameter("@MaGiaoVien", SqlDbType.VarChar, 20, "MaGiaoVien");
            pInsert[3] = new SqlParameter("@NgayHoc", SqlDbType.Date, 10, "NgayHoc");
            pInsert[4] = new SqlParameter("@SoGioDay", SqlDbType.Float, 18, "SoGioDay");
            pInsert[5] = new SqlParameter("@NhanXet", SqlDbType.NVarChar, 200, "NhanXetCuaGiaoVien");

            pUpdate[0] = new SqlParameter("@MaHocTap", SqlDbType.UniqueIdentifier, 18, "MaHocTap");
            pUpdate[1] = new SqlParameter("@MaKhoaHoc", SqlDbType.VarChar, 20, "MaKhoaHoc");
            pUpdate[2] = new SqlParameter("@MaGiaoVien", SqlDbType.VarChar, 20, "MaGiaoVien");
            pUpdate[3] = new SqlParameter("@NgayHoc", SqlDbType.Date, 10, "NgayHoc");
            pUpdate[4] = new SqlParameter("@SoGioDay", SqlDbType.Float, 18, "SoGioDay");
            pUpdate[5] = new SqlParameter("@NhanXet", SqlDbType.NVarChar, 200, "NhanXetCuaGiaoVien");

            pDelete[0] = new SqlParameter("@MaHocTap", SqlDbType.UniqueIdentifier, 18, "MaHocTap");

            //assign all sp into all cmd
            SqlConnection cnn = new SqlConnection(_strCon);

            SqlCommand cmdInsert = new SqlCommand();
            cmdInsert.Connection = cnn;
            cmdInsert.CommandText = "[dbo].[sp_Invoice_KhoaHoc_QTHT_Insert]";
            cmdInsert.CommandType = CommandType.StoredProcedure;

            SqlCommand cmdUpdate = new SqlCommand();
            cmdUpdate.Connection = cnn;
            cmdUpdate.CommandText = "[dbo].[sp_Invoice_KhoaHoc_QTHT_Update]";
            cmdUpdate.CommandType = CommandType.StoredProcedure;

            SqlCommand cmdDelete = new SqlCommand();
            cmdDelete.Connection = cnn;
            cmdDelete.CommandText = "[dbo].[sp_Invoice_KhoaHoc_QTHT_Delete]";
            cmdDelete.CommandType = CommandType.StoredProcedure;

            // Add the parameter to the Parameters collection.
            cmdInsert.Parameters.AddRange(pInsert);
            cmdUpdate.Parameters.AddRange(pUpdate);
            cmdDelete.Parameters.AddRange(pDelete);

            SqlDataAdapter da = new SqlDataAdapter();
            da.InsertCommand = cmdInsert;
            da.UpdateCommand = cmdUpdate;
            da.DeleteCommand = cmdDelete;

            //Update
            try
            {
                da.Update(_ds, "MASTER");
                _ds.Tables["MASTER"].AcceptChanges();

                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);

                return false;
            }
            
        }

        //method execute query into DETAIL
        private Boolean ExecuteIntoDetail() 
        {
            //Declare param and assign value
            SqlParameter[] pInsert = new SqlParameter[6];
            SqlParameter[] pUpdate = new SqlParameter[6];
            SqlParameter[] pDelete = new SqlParameter[1];

            pInsert[0] = new SqlParameter("@MaHocTap", SqlDbType.UniqueIdentifier, 18 ,"MaHocTap");
            pInsert[1] = new SqlParameter("@InvoiceNumber", SqlDbType.VarChar, 12, "InvoiceNumber");
            pInsert[2] = new SqlParameter("@CoMat", SqlDbType.Bit, 1, "CoMat");
            pInsert[3] = new SqlParameter("@TyLeChuyenCan",  SqlDbType.Int, 18, "TyLeChuyenCan");
            pInsert[4] = new SqlParameter("@GhiChu", SqlDbType.NVarChar, 200, "GhiChu");
            //Dien hinh cua cách Insert tablse có field ID va sau khi Insert thì ID của Datatable được map (trùng) với ID của database.
            // 1. Trong sp_... phải có tham số ID là OUTPUT  (@ID int output)
            // 2. Cuối sp_... phải SET @ID = SCOPE_IDENTITY()
            // 3. Trong C# dung parameter có Dicrection là OUTPUT
            pInsert[5] = new SqlParameter("@ID", SqlDbType.NVarChar, 18, "ID");
            pInsert[5].Direction = ParameterDirection.Output;

            pUpdate[0] = new SqlParameter("@MaHocTap", SqlDbType.UniqueIdentifier, 18, "MaHocTap");
            pUpdate[1] = new SqlParameter("@InvoiceNumber", SqlDbType.VarChar, 12, "InvoiceNumber");
            pUpdate[2] = new SqlParameter("@CoMat", SqlDbType.Bit, 1, "CoMat");
            pUpdate[3] = new SqlParameter("@TyLeChuyenCan", SqlDbType.Int, 18, "TyLeChuyenCan");
            pUpdate[4] = new SqlParameter("@GhiChu", SqlDbType.NVarChar, 200, "GhiChu");
            pUpdate[5] = new SqlParameter("@ID", SqlDbType.Int, 18, "ID");

            pDelete[0] = new SqlParameter("@ID", SqlDbType.Int, 18, "ID");

            //assign all sp into all cmd
            SqlConnection cnn = new SqlConnection(_strCon);

            SqlCommand cmdInsert = new SqlCommand();
            cmdInsert.Connection = cnn;
            cmdInsert.CommandText = "[dbo].[sp_Invoice_KhoaHoc_QTHT_ChiTiet_Insert]";
            cmdInsert.CommandType = CommandType.StoredProcedure;
            
            SqlCommand cmdUpdate = new SqlCommand();
            cmdUpdate.Connection = cnn;
            cmdUpdate.CommandText = "[dbo].[sp_Invoice_KhoaHoc_QTHT_ChiTiet_Update]";
            cmdUpdate.CommandType = CommandType.StoredProcedure;

            SqlCommand cmdDelete = new SqlCommand();
            cmdDelete.Connection = cnn;
            cmdDelete.CommandText = "[dbo].[sp_Invoice_KhoaHoc_QTHT_ChiTiet_Delete]";
            cmdDelete.CommandType = CommandType.StoredProcedure;

            // Add the parameter to the Parameters collection.
            cmdInsert.Parameters.AddRange(pInsert);
            cmdUpdate.Parameters.AddRange(pUpdate);
            cmdDelete.Parameters.AddRange(pDelete);

            //execute all cmd
            SqlDataAdapter da = new SqlDataAdapter();
            da.InsertCommand = cmdInsert;
            da.UpdateCommand = cmdUpdate;
            da.DeleteCommand = cmdDelete;

            //Update
            try
            {
                da.Update(_ds, "DETAIL");
                _ds.Tables["DETAIL"].AcceptChanges();
                
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
           
        }

        private void cmdCapNhat_Click(object sender, EventArgs e)
        {
            try
            {
                if (this.ExecuteIntoMaster())
                    this.ExecuteIntoDetail();

                //mapping data to DETAIL
                SqlParameter[] sqlP_D = new SqlParameter[1];
                sqlP_D[0] = new SqlParameter("@MaKhoaHoc", cboKhoaHoc.EditValue.ToString());
                SQLHelper.Fill(_ds.Tables["DETAIL"], _strCon, CommandType.StoredProcedure, "[dbo].[sp_frmDiemDanh_GetHocSinhByLop]", sqlP_D);

                //chkChonHet.Checked = false;  Khoi can, rối

                gvHocSinh.MoveFirst();

                cboKhoaHoc.Enabled = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        private void cmdBoQua_Click(object sender, EventArgs e)
        {
            //refesh dataset
            _ds.RejectChanges();

            //call method load data startup by malop
            //GetDataByLop(cboKhoaHoc.EditValue.ToString());
            cboKhoaHoc.Enabled = true;
        }

        private void cboKhoaHoc_EditValueChanged(object sender, EventArgs e)
        {
            try
            {
                //assgin value for cell GiaoVien
                this.LoadGiaoVienByLop(cboKhoaHoc.EditValue.ToString().Trim());  //Chi la gan DM

                //GetData
                GetDataByLop(cboKhoaHoc.EditValue.ToString().Trim());
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

       
        private void gcGiaoVien_EmbeddedNavigator_ButtonClick(object sender, DevExpress.XtraEditors.NavigatorButtonClickEventArgs e)
        {
            if (e.Button.ButtonType == DevExpress.XtraEditors.NavigatorButtonType.Append)
                {
                    //    NotifyUser(); // optionally
                    try
                    {
                        //Ma lop
                        string MaLop = cboKhoaHoc.EditValue.ToString();
                        //assign value for cell MaHocTap
                        Guid NewMaHocTap = Guid.NewGuid();

                        DataRow M_NewRow = _ds.Tables["MASTER"].NewRow();
                        M_NewRow["MaHocTap"] = NewMaHocTap;
                        M_NewRow["MaKhoaHoc"] = MaLop;
                        M_NewRow["NgayHoc"] = GlobalVariable.WSInfo.WorkingDate;
                        M_NewRow["SoGioDay"] = Convert.ToDecimal(SQLHelper.ExecuteScalar(_strCon, "SELECT CASE WHEN isnull(SoNgayHoc,0) = 0 THEN null ELSE round(convert(float, SoGioHoc)/SoNgayHoc,1) END  FROM DM_KHOAHOC WHERE MaKhoaHoc = '" + MaLop + "'"));
                        _ds.Tables["MASTER"].Rows.Add(M_NewRow);

                        //Dua danh sach hoc sinh cua lop va datatable DETAIL
                        DataTable dt = new DataTable();
                        SQLHelper.Fill(dt, _strCon, "SELECT Invoice_KhoaHoc.InvoiceNumber, Holot, Ten " +
                                                    "FROM Invoice_KhoaHoc " +
                                                    "JOIN Invoice ON Invoice_KhoaHoc.InvoiceNumber = Invoice.InvoiceNumber " +
                                                    "JOIN Enquiry ON Invoice.EnquiryNumber = Enquiry.EnquiryNumber  " +
                                                    "WHERE MaKhoaHoc = '" + MaLop + "'");
                        foreach (DataRow dr in dt.Rows)
                        {
                            DataRow DetailNewRow = _ds.Tables["DETAIL"].NewRow();
                            DetailNewRow["MaHocTap"] = NewMaHocTap;
                            DetailNewRow["InvoiceNumber"] = dr["InvoiceNumber"];
                            DetailNewRow["HoLot"] = dr["HoLot"];
                            DetailNewRow["Ten"] = dr["Ten"];
                            DetailNewRow["CoMat"] = 0;
                            _ds.Tables["DETAIL"].Rows.Add(DetailNewRow);
                        }

                        gcHocSinh.RefreshDataSource();

                        e.Handled = true; // this line prevents adding a row

                        cboKhoaHoc.Enabled = false; //Khoa, khi nao luu moi mo lai de diem danh lop khac
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }

            }
        }

       
        private void chkChonHet_CheckedChanged_1(object sender, EventArgs e)
        {
            BindingManagerBase bmb = this.BindingContext[_ds, "MASTER"];
            if (bmb.Position < 0) return;

            foreach (DataRow dr in _ds.Tables["MASTER"].Rows[bmb.Position].GetChildRows("M_D"))
            {
                dr["CoMat"] = chkChonHet.Checked;
            }
        }

        
        private void splitContainer1_Panel2_SizeChanged(object sender, EventArgs e)
        {
            chkChonHet.Left = splitContainer1.Left + splitContainer1.Panel1.Width;
        }

        private void gvGiaoVien_CustomDrawRowIndicator(object sender, DevExpress.XtraGrid.Views.Grid.RowIndicatorCustomDrawEventArgs e)
        {
            if (e.Info.IsRowIndicator && e.RowHandle > -1)
                e.Info.DisplayText = (e.RowHandle + 1).ToString();
        }

        private void gvHocSinh_CustomDrawRowIndicator(object sender, DevExpress.XtraGrid.Views.Grid.RowIndicatorCustomDrawEventArgs e)
        {
            if (e.Info.IsRowIndicator && e.RowHandle > -1)
                e.Info.DisplayText = (e.RowHandle + 1).ToString();
        }
       

   
    
    }//the end class
}//the end namespace
